package com.chenfan.filestore.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.converters.biginteger.BigIntegerStringConverter;
import com.alibaba.excel.converters.date.DateStringConverter;
import com.alibaba.excel.converters.integer.IntegerStringConverter;
import com.alibaba.excel.converters.longconverter.LongStringConverter;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.fastjson.JSONObject;
import com.chenfan.filestore.config.DynamicDataSourceContextHolder;
import com.chenfan.filestore.mq.task.ImportTaskConsumer;
import com.chenfan.filestore.mq.task.TaskConsumer;
import com.chenfan.filestore.mq.task.TaskMqMsg;
import com.chenfan.filestore.service.TaskService;
import com.chenfan.filestore.utils.excel.DoubleStringConverter;
import com.chenfan.filestore.utils.excel.SqlDateConverter;
import com.chenfan.filestore.utils.excel.TimestampConverter;
import com.chenfan.infra.context.UserContextHolder;
import org.apache.rocketmq.common.message.MessageExt;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @author K
 * @date 2023/6/6 11:44 AM
 */
@RestController
public class RestC {

    @Autowired
    private ImportTaskConsumer importTaskConsumer;
    @Autowired
    private TaskConsumer taskConsumer;

    @Autowired
    private TaskService taskService;

    @GetMapping("/import/{id}")
    public void importExcel(@PathVariable Long id) {
        TaskMqMsg taskMqMsg = new TaskMqMsg();
        taskMqMsg.setTaskId(id);

        MessageExt messageExt = new MessageExt();
        messageExt.setBody(JSONObject.toJSONBytes(taskMqMsg));
        messageExt.putUserProperty("token", UserContextHolder.getToken());
        importTaskConsumer.onMessage(messageExt);
    }

    @GetMapping("/export/{id}")
    public void exportExcel(@PathVariable Long id) {
        TaskMqMsg taskMqMsg = new TaskMqMsg();
        taskMqMsg.setTaskId(id);
        taskConsumer.onMessage(taskMqMsg);
    }

    @GetMapping("/export/test")
    public void exportExcel2() throws IOException {
        InputStream remoteTemplate = new FileInputStream("d:\\9c2d52bb22744a83a74f3b1eaa69ca67.xlsx");
        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write("d:\\aa.xlsx")
                .registerConverter(new IntegerStringConverter())
                .registerConverter(new BigIntegerStringConverter())
                .registerConverter(new DoubleStringConverter())
                .registerConverter(new DateStringConverter())
                .registerConverter(new LongStringConverter())
                .registerConverter(new TimestampConverter())
                .registerConverter(new SqlDateConverter())
                .withTemplate(remoteTemplate);
        ExcelWriter excelWriter = excelWriterBuilder.build();

        //  todo leo 多个sheet页面 待定多少个sheet 模板设置名称失效
        WriteSheet writeSheet = EasyExcel.writerSheet(0,"1111111").build();
//        FillConfig fillConfig = FillConfig.builder().build();
        List<Object> datas = new ArrayList<>();

        String sql = getsql();

        taskService.findByBatch(sql, context -> {
            Object dbMapData = context.getResultObject();
            datas.add(dbMapData);
//            excelWriter.fill(datas,fillConfig,writeSheet);
            excelWriter.fill(datas, writeSheet);
            datas.clear();

        });
        excelWriter.finish();
        remoteTemplate.close();
    }

    public static String getsql(){
        return "SELECT\n" +
                "\tiv.brand_name,\n" +
                "\tREPLACE(iv.inventory_name,concat(SUBSTRING_INDEX(iv.inventory_name, '-',1),\"-\"),\"\") as inventory_name,\n" +
                "\tiv.product_code,\n" +
                "\tiv.color,\n" +
                "\tiv.size,\n" +
                "\tiv.composition,\n" +
                "\tiv.barcode,\n" +
                "\tiv.safety_technology,\n" +
                "\tiv.implementation_standards,\n" +
                "\tiv.qa_standard_name,\n" +
                "\tiv.LEVEL,\n" +
                "\tiv.retail_price,\n" +
                "\tSUBSTRING_INDEX(iv.wmcategory, '：',1) as wmcategory,\n" +
                "CASE\n" +
                "\t\tWHEN iv.is_joint = 0 THEN\n" +
                "\t\t'是' ELSE '否' \n" +
                "\tEND AS is_joint,\n" +
                "\tiv.joint_series_name,\n" +
                "\tiv.joint_brand_name,\n" +
                "\tiv.big_class_name,\n" +
                "\tiv.middle_class_name,\n" +
                "\tiv.small_class_name \n" +
                "FROM\n" +
                "\tinventory iv \n" +
                "\tleft join size_sort ss on (iv.size=ss.size and ss.tenant_id=1)\n" +
                "WHERE\n" +
                "\tiv.state = 0 \n" +
                "\tAND iv.is_delete = 0 \n" +
                "\tAND iv.inventory_type = 0 \n" +
                "\tAND iv.data_source = 0\n" +
                "\t\t\t\tand iv.tenant_id = 1\n" +
                "\t\t\t\tand iv.state = 0\n" +
                "\t\t\t\tand iv.brand_id  in(\n" +
                "\t\t\t\t\t2\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t21\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t3\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t4\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t8\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t26\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t31\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t24\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t27\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t28\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t62\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t29\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t63\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t71\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t64\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t69\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t88\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t36\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t90\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t42\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t52\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t72\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t73\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t75\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t76\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t23\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t22\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t67\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t68\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t80\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t79\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t82\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t81\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t84\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t85\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t83\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t91\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t92\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t93\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t94\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t95\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t97\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t98\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t100\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t101\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t103\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t104\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t105\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t106\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t107\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t111\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t112\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t113\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t114\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t115\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t116\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t117\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t118\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t119\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t120\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t121\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t123\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t124\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t125\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t126\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t127\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t128\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t129\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t130\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t131\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t132\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t133\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t77\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t86\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t78\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t-2\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t135\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t136\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t137\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t138\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t139\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t144\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t145\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t146\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t148\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t149\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t150\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t151\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t152\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t157\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t158\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t159\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t160\n" +
                "\t\t\t\t)\n" +
                "\t\t\t\tand iv.brand_id  in(\n" +
                "\t\t\t\t\t29\n" +
                "\t\t\t\t)\n" +
                "\t\t\t\tAND iv.company_id  in(\n" +
                "\t\t\t\t\t2\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t3\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t4\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t5\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t6\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t8\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t9\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t10\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t11\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t12\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t13\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t15\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t1340926979100119041\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t1349997941976207361\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t1374998291715985410\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t1455793633345245185\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t1455793863944011777\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t1473546963479793666\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t1514076154360987649\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t1518057638235410434\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t1587685962459762690\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t1656243664723218434\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t1658306376454213634\n" +
                "\t\t\t\t\t\t,\n" +
                "\t\t\t\t\t1\n" +
                "\t\t\t\t)\n" +
                "\t\t\t\tAND iv.product_code like concat('%','TZP230801','%')\n" +
                "\t\t\t\tAND iv.create_date BETWEEN '2023-08-01 00:00:00' AND '2023-08-15 23:59:59'\n" +
                "ORDER BY\n" +
                "iv.spu_date DESC,\n" +
                "iv.color ASC,\n" +
                "ss.sort ASC";
    }
}
